﻿using Coldairarrow.Entity.Base_Manage;
using Coldairarrow.Entity.Interface;
using Coldairarrow.Util;
using Coldairarrow.Util.Helper;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using Microsoft.Extensions.Primitives;
using Newtonsoft.Json.Linq;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Reflection;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using static Npgsql.PostgresTypes.PostgresCompositeType;

namespace Coldairarrow.Business.Interface
{
    public class SqlDBInterfaceBusiness
    {
        //索引字段
        public List<string> indexFields { get; set; } = new List<string>();

        public List<ComparerKeys> Keys { get; set; } = new List<ComparerKeys>();

        public SqlServerHelper SqlDb { get; set; }

        public string ConString { get; set; }

        public int Count { get; set; }
        public SqlDBInterfaceBusiness(List<ComparerKeys> keys)
        {
            //数据库连接
            ConString = JsonSettingHelper.GetSetting("Database:BaseDb:ConnectionString");
            SqlDb = (SqlServerHelper)DbHelperFactory.GetDbHelper(EFCore.Sharding.DatabaseType.SqlServer, ConString);
            this.Keys = keys.GroupBy(x => x.Field).Select(x => x.First()).ToList();
            indexFields = Keys.Where(x => x.Type == "K").Select(x => x.Field).ToList();
            Count = 10000;
        }
        /// <summary>
        /// 执行增删改查业务
        /// </summary>
        public async void ExecuteInterface<T>(List<T> sourceData, string tableName, string where = "", bool isDelete = true, bool isUpdata = true)
        {
            try
            {
                Type type = typeof(T);
                var proInfoList = type.GetProperties().ToList();
                string sqlStr_select = $"SELECT * FROM {tableName} {where}";
                List<T> targetData = this.SqlDb.GetListBySql<T>(sqlStr_select);
                //sourceData.Where(x => !x.GetPropertyValue("RowId").IsNullOrEmpty()).ForEach(x => x.SetPropertyValue("RowId", ""));
                if (sourceData.Count > 0)
                {
                    //更新
                    if (isUpdata)
                    {

                        #region
                        List<T> updataItem = sourceData.Intersect(targetData, new ComparerFromKeys_SQL<T>(Keys)).ToList();//获取交集(更新项)
                        Update<T>(updataItem, tableName, Keys, type, proInfoList);//执行更新
                        #endregion
                    }
                    //新增
                    #region
                    List<T> insertItem = sourceData.Except(targetData, new ComparerFromKey_SQL<T>(indexFields.First())).ToList();//获取源数据对目标数据的差集(新增项)
                    Insert<T>(insertItem, tableName, Keys, type, proInfoList);//执行插入行
                    #endregion
                    //删除
                    #region
                    if (isDelete)
                    {
                        List<T> deleteItem = targetData.Except(sourceData, new ComparerFromKey_SQL<T>(indexFields.First())).ToList();//获取目标数对据源数据的差集(删除项)
                        List<T> repeatItem = targetData.GroupBy(x => x.GetPropertyValue(indexFields.First())).Where(x => x.Count() > 1).Select(x => x.FirstOrDefault()).ToList();
                        //将重复的项也一起加入删除的列表
                        deleteItem.AddRange(repeatItem);
                        //筛选出删除列表中重复的项
                        var temp = deleteItem.GroupBy(x => x.GetPropertyValue(indexFields.First())).Where(x => x.Count() > 1).Select(x => x.FirstOrDefault()).ToList();
                        deleteItem.AddRange(temp);
                        Delete<T>(deleteItem, tableName, Keys, type, proInfoList);//执行删除行
                    }
                    else
                    {
                        List<T> deleteItem = new List<T>();
                        List<T> repeatItem = targetData.GroupBy(x => x.GetPropertyValue(indexFields.First())).Where(x => x.Count() > 1).Select(x => x.FirstOrDefault()).ToList();
                        //将重复的项也一起加入删除的列表
                        deleteItem.AddRange(repeatItem);
                        //筛选出删除列表中重复的项
                        var temp = deleteItem.GroupBy(x => x.GetPropertyValue(indexFields.First())).Where(x => x.Count() > 1).Select(x => x.FirstOrDefault()).ToList();
                        deleteItem.AddRange(temp);
                        Delete<T>(deleteItem, tableName, Keys, type, proInfoList);//执行删除行

                    }
                    #endregion
                }
            }
            catch (Exception ex)
            {
                AddLog(SqlDb, ex.Message, $"数据增删改：{typeof(T).FullName}", "erro");

            }

        }
        /// <summary>
        /// 更新
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="updataItem"></param>
        /// <param name="Keys"></param>
        public void Update<T>(List<T> updataItem, string tableName, List<ComparerKeys> Keys, Type type, List<PropertyInfo> proInfoList)
        {
            StringBuilder sb = new StringBuilder();
            try
            {

                if (!updataItem.IsNullOrEmpty() && updataItem.Count > 0)
                {

                    string sqlStr = $"UPDATE {tableName} SET @fields WHERE @where";
                    int index = 0;
                    //将所有数据循环生成更新的SQL语句

                    while (updataItem.Count > 0)
                    {
                        sb.Clear();
                        //防止数据量过大，分批循环
                        index = updataItem.Count < Count ? updataItem.Count : Count;
                        List<T> updataItem_ = updataItem.GetRange(0, index);
                        foreach (var data in updataItem_)
                        {
                            string sql = "";
                            string where = "";
                            List<string> fields = new List<string>();
                            foreach (var item in Keys)
                            {
                                //类型为N的字段不需要更新
                                if (item.Type != "N")
                                {


                                    //判断需要更新的字段是否存在
                                    var p = proInfoList.Where(x => x.Name == item.Field);
                                    if (!p.IsNullOrEmpty() && p.Count() > 0)
                                    {
                                        var name = p.First().Name;
                                        var value = data.GetPropertyValue(name);
                                        value = !value.IsNullOrEmpty() && value.ToString().Contains("'") ? value.ToString().Replace("'", "''") : value;
                                        if (item.Type == "K")
                                        {
                                            where = $"[{name}]='{value}'";
                                        }
                                        else
                                        {
                                            fields.Add($"[{name}]='{value}'");
                                        }
                                    }

                                }

                            }
                            sql = sqlStr.Replace("@fields", string.Join(",", fields)).Replace("@where", where) + "\r\n";
                            sb.Append(sql);
                        }
                        var str = sb.ToString();
                        this.SqlDb.ExecuteSql(sb.ToString());
                        updataItem.RemoveRange(0, index);
                    }
                }
            }
            catch (Exception ex)
            {
                AddLog(SqlDb, ex.Message, $"数据库新增：{typeof(T).FullName}", "erro", "");
            }
        }


        /// <summary>
        /// 新增
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="insertItem"></param>
        /// <param name="Keys"></param>
        public void Insert<T>(List<T> insertItem, string tableName, List<ComparerKeys> Keys, Type type, List<PropertyInfo> proInfoList)
        {
            StringBuilder sb = new StringBuilder();
            try
            {
                if (!insertItem.IsNullOrEmpty() && insertItem.Count > 0)
                {
                    string sqlStr = $"INSERT INTO {tableName}(@fields) VALUES(@values)";
                    int index = 0;
                    //将所有数据循环生成更新的SQL语句
                    while (insertItem.Count > 0)
                    {
                        //防止数据量过大，分批循环
                        sb.Clear();
                        index = insertItem.Count < Count ? insertItem.Count : Count;
                        List<T> insertItem_ = insertItem.GetRange(0, index);
                        foreach (var data in insertItem_)
                        {
                            string sql = "";
                            List<string> fields = new List<string>();
                            List<string> values = new List<string>();
                            foreach (var item in Keys)
                            {
                                //类型为N的字段不需要更新
                                if (item.Type != "N")
                                {

                                    //判断需要更新的字段是否存在
                                    var p = proInfoList.Where(x => x.Name == item.Field);
                                    if (!p.IsNullOrEmpty() && p.Count() > 0)
                                    {
                                        var name = p.First().Name;
                                        fields.Add($"[{name}]");
                                        var value = data.GetPropertyValue(name);
                                        value = !value.IsNullOrEmpty() && value.ToString().Contains("'") ? value.ToString().Replace("'", "''") : value;
                                        values.Add($"'{value}'");
                                    }

                                }

                            }
                            sql = sqlStr.Replace("@fields", string.Join(",", fields)).Replace("@values", string.Join(",", values)) + "\r\n";
                            sb.Append(sql);
                        }
                        var sqlstr = sb.ToString();
                        this.SqlDb.ExecuteSql(sb.ToString());
                        insertItem.RemoveRange(0, index);
                    }

                }
            }
            catch (Exception ex)
            {
                AddLog(SqlDb, ex.Message, $"数据库新增：{typeof(T).FullName}", "erro", "");
            }
        }

        //String SQLSTE { get; set; }

        /// <summary>
        /// 删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="deleteItem"></param>
        /// <param name="Keys"></param>
        public void Delete<T>(List<T> deleteItem, string tableName, List<ComparerKeys> Keys, Type type, List<PropertyInfo> proInfoList)
        {
            StringBuilder sb = new StringBuilder();
            try
            {
                if (!deleteItem.IsNullOrEmpty() && deleteItem.Count > 0)
                {
                    string sqlStr = $"DELETE {tableName} WHERE @where";
                    int index = 0;
                    //将所有数据循环生成更新的SQL语句
                    while (deleteItem.Count > 0)
                    {
                        sb.Clear();
                        //防止数据量过大，分批循环
                        index = deleteItem.Count < Count ? deleteItem.Count : Count;
                        List<T> deleteItem_ = deleteItem.GetRange(0, index);
                        foreach (var data in deleteItem_)
                        {
                            string sql = "";
                            string where = "";
                            StringBuilder fields = new StringBuilder();
                            foreach (var item in Keys)
                            {
                                //类型为N的字段不需要更新
                                if (item.Type == "K")
                                {
                                    //判断需要更新的字段是否存在
                                    var p = proInfoList.Where(x => x.Name == item.Field);
                                    if (!p.IsNullOrEmpty() && p.Count() > 0)
                                    {
                                        var name = p.First().Name;
                                        var value = data.GetPropertyValue(name);
                                        where = $"[{name}]='{value}'";
                                    }

                                }

                            }
                            sql = sqlStr.Replace("@where", where) + "\r\n";
                            sb.Append(sql);
                        }

                        this.SqlDb.ExecuteSql(sb.ToString());
                        deleteItem.RemoveRange(0, index);
                    }
                }
            }
            catch (Exception ex)
            {
                AddLog(SqlDb, ex.Message, $"数据库新增：{typeof(T).FullName}", "erro",  "");
            }
        }


        /// <summary>
        /// 添加接口日志
        /// </summary>
        /// <param name="logContent"></param>
        /// <param name="inerfaceName"></param>
        /// <param name="db"></param>
        /// <param name="inerfaceData"></param>
        /// <param name="logType"></param>
        public void AddLog(SqlServerHelper db, string logContent, string inerfaceName, string logType = " ", string inerfaceData = " ")
        {
            try
            {
                db._conStrings[0] = JsonSettingHelper.GetSetting("Database:BaseDb:ConnectionString");
                List<DbParameter> parameters = new List<DbParameter>();
                parameters.Add(SqlStringHelper.CreateParameter("@id", IdHelper.GetId()));
                parameters.Add(SqlStringHelper.CreateParameter("@createTime", DateTime.Now));
                parameters.Add(SqlStringHelper.CreateParameter("@creatorRealName", ""));
                parameters.Add(SqlStringHelper.CreateParameter("@creatorId", "Admin"));
                parameters.Add(SqlStringHelper.CreateParameter("@logType", logType));
                parameters.Add(SqlStringHelper.CreateParameter("@logContent", logContent));
                parameters.Add(SqlStringHelper.CreateParameter("@InterfaceName", inerfaceName));
                parameters.Add(SqlStringHelper.CreateParameter("@inerfaceData", inerfaceData));
                string sqlStr = @"INSERT INTO 
           Base_InterfaceLog(Id,CreateTime,CreatorRealName,CreatorId,LogType,LogContent,InterfaceName,InterfaceData) 
            VALUES(@id,@createTime,@creatorRealName,@creatorId,@logType,@logContent,@InterfaceName,@inerfaceData)";

                db.ExecuteSql(sqlStr, parameters);
            }
            catch (Exception ex)
            {


            }
        }



    }


}
